{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 设置对齐方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import Alignment\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['B1'] = \"两端对齐\"\n",
    "a1 = ws['B1']\n",
    "a1.alignment = Alignment(horizontal = \"justify\")\n",
    "\n",
    "\n",
    "ws['B2'] = \"填满对齐\"\n",
    "a1 = ws['B2']\n",
    "a1.alignment = Alignment(horizontal = \"fill\")\n",
    "\n",
    "ws['B3'] = \"填满对齐填满对齐填满对齐\"\n",
    "a1 = ws['B3']\n",
    "a1.alignment = Alignment(horizontal = \"fill\")\n",
    "\n",
    "ws['B4'] = \"左对齐\"\n",
    "a1 = ws['B4']\n",
    "a1.alignment = Alignment(horizontal = \"left\")\n",
    "\n",
    "ws['B5'] = \"右对齐\"\n",
    "a1 = ws['B5']\n",
    "a1.alignment = Alignment(horizontal = \"right\")\n",
    "\n",
    "ws['B6'] = \"一般对齐\"\n",
    "a1 = ws['B6']\n",
    "a1.alignment = Alignment(horizontal = \"general\")\n",
    "\n",
    "ws['B7'] = \"一般对齐一般对齐一般对齐\"\n",
    "a1 = ws['B7']\n",
    "a1.alignment = Alignment(horizontal = \"general\")\n",
    "\n",
    "ws['B8'] = \"居中对齐\"\n",
    "a1 = ws['B8']\n",
    "a1.alignment = Alignment(horizontal = \"center\")\n",
    "\n",
    "ws['B9'] = \"分散对齐\"\n",
    "a1 = ws['B9']\n",
    "a1.alignment = Alignment(horizontal = \"distributed\")\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\alignment_horizontal.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import Alignment\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['A1'] = \"底部对齐\"\n",
    "a1 = ws['A1']\n",
    "a1.alignment = Alignment(vertical = \"bottom\")\n",
    "\n",
    "ws['B2'] = \"居中对齐\"\n",
    "a1 = ws['B2']\n",
    "a1.alignment = Alignment(vertical = \"center\")\n",
    "\n",
    "ws['C3'] = \"上部对齐\"\n",
    "a1 = ws['C3']\n",
    "a1.alignment = Alignment(vertical = \"top\")\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\alignment_vertical.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import Alignment\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "ws['A1'] = \"我要旋转30度\"\n",
    "a1 = ws['A1']\n",
    "a1.alignment = Alignment(text_rotation = 30)\n",
    "\n",
    "ws['B2'] = \"我不换行我不换行\"\n",
    "\n",
    "ws['C3'] = \"我要换行我要换行\"\n",
    "a1 = ws['C3']\n",
    "a1.alignment = Alignment(wrap_text = True)\n",
    "\n",
    "ws['D4'] = \"我会自己调整大小\"\n",
    "a1 = ws['D4']\n",
    "a1.alignment = Alignment(shrink_to_fit = True)\n",
    "\n",
    "ws['E5'] = \"缩进2个字符\"\n",
    "a1 = ws['E5']\n",
    "a1.alignment = Alignment(inde nt = 2)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\alignment_other.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 合并与解除单元格"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.workbook import Workbook\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "ws[\"A2\"] = \"我是合并单元格\"\n",
    "ws.merge_cells('A2:D2') #合并单元格\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\merge.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.workbook import Workbook\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "ws[\"A2\"] = \"我是合并单元格\"\n",
    "ws.merge_cells('A2:D2') #合并单元格\n",
    "ws.unmerge_cells('A2:D2') #解除合并\n",
    "\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\unmerge.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 设置合并单元格样式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [],
   "source": [
    "from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment\n",
    "from openpyxl import Workbook\n",
    "\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "ws.merge_cells('B2:F4')\n",
    "\n",
    "top_left_cell = ws['B2']\n",
    "top_left_cell.value = \"My Cell\"\n",
    "\n",
    "top_left_cell.fill = PatternFill(\"solid\", fgColor=\"DDDDDD\")\n",
    "top_left_cell.font  = Font(bold=True, color=\"FF0000\")\n",
    "top_left_cell.alignment = Alignment(horizontal=\"center\", vertical=\"center\")\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\styled.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 案例：批量设置单元格对齐方式"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "#对全部数据进行设置(通过列进行设置)\n",
    "from openpyxl import Workbook\n",
    "from openpyxl.styles import colors\n",
    "from openpyxl.styles import Font\n",
    "from openpyxl.styles import PatternFill\n",
    "from openpyxl.styles import Border, Side\n",
    "from openpyxl.styles import Alignment\n",
    "wb = Workbook()\n",
    "ws = wb.active\n",
    "\n",
    "rows = [\n",
    "    ['xxx指标情况','',''],\n",
    "    ['col_1', 'col_2', 'col_3'],\n",
    "    [2, 30, 30],\n",
    "    [3, 30, 25],\n",
    "    [4, 40, 30],\n",
    "    [5, 20, 10],\n",
    "    [6, 25, 5],\n",
    "    [7, 50, 10],\n",
    "]\n",
    "\n",
    "for row in rows:\n",
    "    ws.append(row)\n",
    "\n",
    "for col in ws[\"A\":\"C\"]:\n",
    "    for c in col:\n",
    "        c.font = Font(name = '微软雅黑',size = 12,color = \"FFFFFFFF\")\n",
    "        c.fill = PatternFill(fill_type = 'solid',start_color='FFFF6100')\n",
    "        c.border = Border(left = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   right = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   top = Side(border_style = \"thin\",color = \"FF0F0F0F\"),\n",
    "                   bottom = Side(border_style = \"thin\",color = \"FF0F0F0F\"))\n",
    "        c.alignment = Alignment(horizontal = \"center\",vertical = \"center\")\n",
    "\n",
    "#合并单元格设置\n",
    "ws.merge_cells('A1:C1')\n",
    "top_left_cell = ws['A1']\n",
    "top_left_cell.font  = Font(name = '微软雅黑',size = 12,color = \"FFFFFFFF\",bold=True)\n",
    "\n",
    "wb.save(r'C:\\Users\\zhangjunhong\\Desktop\\all_cell_alignment_style.xlsx')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {
    "height": "789px",
    "left": "69px",
    "top": "110px",
    "width": "308.514px"
   },
   "toc_section_display": true,
   "toc_window_display": true
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
